package JPetStore.persistence.impl;

import JPetStore.domain.Account;
import JPetStore.persistence.AccountDao;
import JPetStore.persistence.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class AccountDaoImpl implements AccountDao {

    private static final String GET_ACCOUNT_BY_USERNAME_AND_PASSWORD = "SELECT " +
            "SIGNON.USERNAME," +
            "ACCOUNT.EMAIL,ACCOUNT.FIRSTNAME,ACCOUNT.LASTNAME,ACCOUNT.STATUS," +
            "ACCOUNT.ADDR1 AS address1,ACCOUNT.ADDR2 AS address2," +
            "ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,ACCOUNT.COUNTRY,ACCOUNT.PHONE," +
            "PROFILE.LANGPREF AS languagePreference,PROFILE.FAVCATEGORY AS favouriteCategoryId," +
            "PROFILE.MYLISTOPT AS listOption,PROFILE.BANNEROPT AS bannerOption," +
            "BANNERDATA.BANNERNAME " +
            "FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA " +
            "WHERE ACCOUNT.USERID = ? AND SIGNON.PASSWORD = ? " +
            "AND SIGNON.USERNAME = ACCOUNT.USERID " +
            "AND PROFILE.USERID = ACCOUNT.USERID " +
            "AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";

    private static final String INSERT_ACCOUNT =
            "INSERT INTO account (userid,email,firstname,lastname,status,addr1,addr2,city,state,zip,country,phone) " +
            "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
    private static final String INSERT_PROFILE =
            "INSERT INTO profile (userid,langpref,favcategory,mylistopt,banneropt) " +
            "VALUES (?,?,?,?,?)";
    private static final String INSERT_SIGNON =
            "INSERT INTO signon (username,password) " +
            "VALUES (?,?)";

    @Override
    public Account getAccountByUsername(String username) {
        return null;
    }

    @Override
    public Account getAccountByUsernameAndPassword(Account account) {
        Account accountResult = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ACCOUNT_BY_USERNAME_AND_PASSWORD);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getPassword());
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                accountResult = this.resultSetToAccount(resultSet);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return accountResult;
    }

    private Account resultSetToAccount(ResultSet resultSet) throws Exception{
        Account account = new Account();
        account.setUsername(resultSet.getString("username"));
//        account.setPassword(resultSet.getString("password"));
        account.setEmail(resultSet.getString("email"));
        account.setFirstName(resultSet.getString("firstName"));
        account.setLastName(resultSet.getString("lastName"));
        account.setStatus(resultSet.getString("status"));
        account.setAddress1(resultSet.getString("address1"));
        account.setAddress2(resultSet.getString("address2"));
        account.setCity(resultSet.getString("city"));
        account.setState(resultSet.getString("state"));
        account.setZip(resultSet.getString("zip"));
        account.setCountry(resultSet.getString("country"));
        account.setPhone(resultSet.getString("phone"));
        account.setFavouriteCategoryId(resultSet.getString("favouriteCategoryId"));
        account.setLanguagePreference(resultSet.getString("languagePreference"));
        account.setListOption(resultSet.getInt("listOption") == 1);
        account.setBannerOption(resultSet.getInt("bannerOption") == 1);
        account.setBannerName(resultSet.getString("bannerName"));
        return account;
    }

    @Override
    public void insertAccount(Account account) {
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_ACCOUNT);
            preparedStatement.setString(1,account.getUserid());
            preparedStatement.setString(2,account.getEmail());
            preparedStatement.setString(3,account.getFirstName());
            preparedStatement.setString(4,account.getLastName());
            preparedStatement.setString(5,account.getStatus());
            preparedStatement.setString(6,account.getAddress1());
            preparedStatement.setString(7,account.getAddress2());
            preparedStatement.setString(8,account.getCity());
            preparedStatement.setString(9,account.getState());
            preparedStatement.setString(10,account.getZip());
            preparedStatement.setString(11,account.getCountry());
            preparedStatement.setString(12,account.getPhone());
            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("account 插入成功");
            }
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void insertProfile(Account account) {
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_PROFILE);
            preparedStatement.setString(1,account.getUserid());
            preparedStatement.setString(2,account.getLanguagePreference());
            preparedStatement.setString(3,account.getFavouriteCategoryId());
            preparedStatement.setInt(4,account.isListOption()?1:0);
            preparedStatement.setInt(5,account.isBannerOption()?1:0);
            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("profile 插入成功");
            }
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void insertSignon(Account account) {
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_SIGNON);
            preparedStatement.setString(1,account.getUsername());
            preparedStatement.setString(2,account.getPassword());
            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("signon 插入成功");
            }
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void updateAccount(Account account) {

    }

    @Override
    public int changeInformation(Account account) {
        int result = 0;

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement("update account set firstname = ?,lastname = ?,email = ?,phone = ?,addr1 = ?,addr2 = ?,city = ?,state = ?,status = ?,zip = ?,country = ? where userid = ?");
            pStatement.setString(1, account.getFirstName());
            pStatement.setString(2, account.getLastName());
            pStatement.setString(3, account.getEmail());
            pStatement.setString(4, account.getPhone());
            pStatement.setString(5, account.getAddress1());
            pStatement.setString(6, account.getAddress2());
            pStatement.setString(7, account.getCity());
            pStatement.setString(8, account.getState());
            pStatement.setString(9, account.getStatus());
            pStatement.setString(10, account.getZip());
            pStatement.setString(11, account.getCountry());
            pStatement.setString(12, account.getUsername());
            result = pStatement.executeUpdate();
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception var5) {
            var5.printStackTrace();
        }

        return result;
    }

    @Override
    public void updateProfile(Account account) {

    }

    @Override
    public void updateSignon(Account account) {

    }

    public boolean is_repeat(String username) {
        boolean is_repeat = false;

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement("select * from signon where username = ?");
            pStatement.setString(1, username);
            ResultSet resultSet = pStatement.executeQuery();
            if (resultSet.next()) {
                is_repeat = true;
            }

            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception var6) {
            var6.printStackTrace();
        }

        return is_repeat;
    }

    public boolean is_exist(String username, String password) {
        boolean is_exist = false;

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement("select * from signon where username =?  and password=? ");
            pStatement.setString(1, username);
            pStatement.setString(2, password);
            ResultSet resultSet = pStatement.executeQuery();
            if (resultSet.next()) {
                is_exist = true;
            }

            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception var7) {
            var7.printStackTrace();
        }

        return is_exist;
    }

    public int addAccount(Account account) {
        int result = 0;

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement("insert into account( userid, email, firstname, lastname, status, addr1, addr2, city, state, zip, country, phone)values(?,?,?,?,?,?,?,?,?,?,?,?)");
            pStatement.setString(1, account.getUsername());
            pStatement.setString(2, account.getEmail());
            pStatement.setString(3, account.getFirstName());
            pStatement.setString(4, account.getLastName());
            pStatement.setString(5, account.getStatus());
            pStatement.setString(6, account.getAddress1());
            pStatement.setString(7, account.getAddress2());
            pStatement.setString(8, account.getCity());
            pStatement.setString(9, account.getState());
            pStatement.setString(10, account.getZip());
            pStatement.setString(11, account.getCountry());
            pStatement.setString(12, account.getPhone());
            result = pStatement.executeUpdate();
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception var5) {
            var5.printStackTrace();
        }

        return result;
    }

    public int addSignOn(Account account) {
        int result = 0;

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement("insert into signon( username, password) values(?,?)");
            pStatement.setString(1, account.getUsername());
            pStatement.setString(2, account.getPassword());
            result = pStatement.executeUpdate();
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception var5) {
            var5.printStackTrace();
        }

        return result;
    }

    public int changePassword(String username, String password) {
        int result = 0;

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement("update signon set password = ? where username = ? ");
            pStatement.setString(1, password);
            pStatement.setString(2, username);
            result = pStatement.executeUpdate();
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception var6) {
            var6.printStackTrace();
        }

        return result;
    }
    
    public int changeUsername(String newusername, String username) {
        int result = 0;

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement("update signon set username = ? where username = ?");
            pStatement.setString(1, newusername);
            pStatement.setString(2, username);
            result = pStatement.executeUpdate();
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception var8) {
            var8.printStackTrace();
        }

        int result1 = 0;

        try {
            Connection connection1 = DBUtil.getConnection();
            PreparedStatement pStatement1 = connection1.prepareStatement("update account set userid = ? where userid = ?");
            pStatement1.setString(1, newusername);
            pStatement1.setString(2, username);
            result1 = pStatement1.executeUpdate();
            DBUtil.closePreparedStatement(pStatement1);
            DBUtil.closeConnection(connection1);
        } catch (Exception var7) {
            var7.printStackTrace();
        }

        return result * result1;
    }
}
